import pymysql
import numpy as np


# 连接 MySQL 数据库
conn = pymysql.connect(host='localhost', user='root', password='root', database='StudentInfo')

# 执行 SQL 查询语句，获取数据
cursor = conn.cursor()
cursor.execute('SELECT userid, course_id,grade FROM users_grades')
data = cursor.fetchall()

# 关闭连接
cursor.close()
conn.close()

user_ids = list(set([d[1] for d in data]))
course_ids = list(set([d[2] for d in data]))
user_index = {user_ids[i]: i for i in range(len(user_ids))}
item_index = {course_ids[i]: i for i in range(len(course_ids))}
R = np.zeros((len(user_ids), len(course_ids)))
for d in data:
    user_id = d[1]
    item_id = d[2]
    score = d[3]
    R[user_index[user_id], item_index[item_id]] = score
print(R)